﻿using Helper.Models;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Helper
{
    public static class DataAccess
    {
        private static Task<decimal> GetBalanceBroughtForwardAsync(int studentID, DateTime? startDate)
        {
            return Task.Run<decimal>(()=>{
            string mDate = "";
            if (startDate.HasValue)
                mDate = startDate.Value.ToString("dd-MM-yyyy");
            else
                mDate = DateTime.Now.ToString("dd-MM-yyyy");
            string salesStr = "DECLARE  @sal decimal=(SELECT SUM(ISNULL(CONVERT(DECIMAL,TotalAmt),0)) FROM  " +
"[School].[SalesOrderHeader] WHERE StudentID ='" + studentID + "' AND CONVERT(DATE, OrderDate) < '" +
mDate + "');\r\n" +

"DECLARE  @pur decimal=(SELECT SUM(ISNULL(CONVERT(DECIMAL,AmountPaid),0)) FROM  " +
"[School].[FeesPayment] WHERE StudentID ='" + studentID + "' AND CONVERT(DATE, DatePaid) < '" +
mDate + "');\r\n" +
"select @sal-@pur";

            decimal ft;
            decimal.TryParse(DataAccessHelper.ExecuteScalar(salesStr), out ft);
            return ft;
            });
        }

        public static Task<FeesStatementModel> GetFeesStatementAsync(int studentID, DateTime? startTime, DateTime? endTime)
        {
            return Task.Run<FeesStatementModel>(async() =>
            {
                if (studentID<=0)

                    return new FeesStatementModel();


                FeesStatementModel temp = new FeesStatementModel();
                decimal amt;
                DateTime dt;
                //debit is an invoice to customer and credit is payment by customer

                string salesStr = "SELECT SalesOrderID,OrderDate, TotalAmt FROM " +
                            "[School].[SalesOrderHeader] WHERE [StudentID] ='" + studentID + "'";
                if (startTime.HasValue && endTime.HasValue && true)
                    salesStr += " AND CONVERT(DATE, OrderDate) BETWEEN '" + startTime.Value.ToString("dd-MM-yyyy") +
                        "' AND '" + endTime.Value.ToString("dd-MM-yyyy") + "'";

                string paymentStr = "SELECT FeesPaymentID, DatePaid, AmountPaid FROM " +
                            "[School].[FeesPayment]  WHERE [StudentID] ='" + studentID + "'";
                if (startTime.HasValue && endTime.HasValue && true)
                    paymentStr += " AND CONVERT(DATE, DatePaid) BETWEEN '" + startTime.Value.ToString("dd-MM-yyyy") +
                        "' AND '" + endTime.Value.ToString("dd-MM-yyyy") + "'";

                DataTable dtSales = DataAccessHelper.ExecuteNonQueryWithResultTable(salesStr);
                DataTable dtPayments = DataAccessHelper.ExecuteNonQueryWithResultTable(paymentStr);
                ObservableCollection<TransactionModel> ish =
                    new ObservableCollection<TransactionModel>();
                foreach (DataRow dts in dtSales.Rows)
                {
                    DateTime.TryParse(dts[1].ToString(), out dt);
                    decimal.TryParse(dts[2].ToString(), out amt);
                    ish.Add(new TransactionModel((int)dts[0],
                        TransactionTypes.Debit, dt, "INVOICE", amt));
                }
                foreach (DataRow dts in dtPayments.Rows)
                {
                    DateTime.TryParse(dts[1].ToString(), out dt);
                    decimal.TryParse(dts[2].ToString(), out amt);
                    ish.Add(new TransactionModel((int)dts[0],
                        TransactionTypes.Credit, dt, "PAYMENT", amt));
                }
                IEnumerable<TransactionModel> qa =
                  from fruit in ish
                  orderby fruit.DateOfTransaction
                  select fruit;
                decimal bbf = await GetBalanceBroughtForwardAsync(studentID, startTime.Value);
                temp.Transactions.Add(new TransactionModel(bbf));
                foreach (TransactionModel cc in qa)
                {
                    temp.Transactions.Add(cc);
                }
                foreach (TransactionModel cc in temp.Transactions)
                {
                    if (cc.TransactionID >0)
                    {
                        if (cc.TransactionType == TransactionTypes.Credit)
                            temp.TotalAmt -= cc.TransactionAmt;
                        else if (cc.TransactionType == TransactionTypes.Debit)
                            temp.TotalAmt += cc.TransactionAmt;
                    }

                }

                temp.StudentID = studentID;
                temp.StartDate = startTime.Value;
                temp.EndDate = endTime.Value;
                return temp;
            });
        }

        public static Task<StudentModel> GetStudentAsync(int StudentID)
        {
            return Task.Run<StudentModel>(() =>
                {
                    StudentModel CurrentStudent = new StudentModel();
                    try
                    {
                        string SELECTSTR =
                               "SELECT FirstName,LastName,MiddleName,ClassID,DateOfBirth," +
                               "DateOfAdmission,NameOfGuardian,GuardianPhoneNo,Email," +
                               "Address,City,PostalCode,PreviousSchool,DormitoryID,BedNo,SPhoto" +
                               " FROM [School].[Student] WHERE StudentID='" + StudentID + "'";
                        DataTable r = DataAccessHelper.ExecuteNonQueryWithResultTable(SELECTSTR);
                        if (r.Rows.Count != 0)
                        {
                            CurrentStudent.StudentID = StudentID;
                            CurrentStudent.FirstName = r.Rows[0][0].ToString();
                            CurrentStudent.LastName = r.Rows[0][1].ToString();
                            CurrentStudent.MiddleName = r.Rows[0][2].ToString();
                            
                            CurrentStudent.ClassID = int.Parse(r.Rows[0][3].ToString());
                            CurrentStudent.DateOfBirth = DateTime.Parse(r.Rows[0][4].ToString());
                            CurrentStudent.DateOfAdmission = DateTime.Parse(r.Rows[0][5].ToString());
                            CurrentStudent.NameOfGuardian = r.Rows[0][6].ToString();
                            CurrentStudent.GuardianPhoneNo = r.Rows[0][7].ToString();
                            CurrentStudent.Email = r.Rows[0][8].ToString();
                            CurrentStudent.Address = r.Rows[0][9].ToString();
                            CurrentStudent.City = r.Rows[0][10].ToString();
                            CurrentStudent.PostalCode = r.Rows[0][11].ToString();
                            CurrentStudent.PrevSchool = r.Rows[0][12].ToString();
                            CurrentStudent.DormitoryID = r.Rows[0][13].ToString();
                            CurrentStudent.BedNo = r.Rows[0][14].ToString();                            
                            CurrentStudent.SPhoto = (byte[])r.Rows[0][15];
                        }
                    }
                    catch { }
                    return CurrentStudent;
                });
        }

        public static ObservableCollection<DormModel> GetAllDorms()
        {
            string selectStr =
                "SELECT DormitoryID,NameOfDormitory FROM [Users].Dormitory";
            DataTable tempColl = DataAccessHelper.ExecuteNonQueryWithResultTable(selectStr);
            ObservableCollection<DormModel> tempCls = new ObservableCollection<DormModel>();
            try
            {
                foreach (DataRow s in tempColl.Rows)
                {
                    tempCls.Add(new DormModel((int)s[0], s[1].ToString()));
                }
            }
            catch { }


            return tempCls;
        }

        public static bool StudentExists(int studentid)
        {
            string selectStr =
                "SELECT StudentID FROM [School].[Student] WHERE StudentID ='" + studentid + "'";
            string result = DataAccessHelper.ExecuteScalar(selectStr);
            if (!string.IsNullOrWhiteSpace(result))
            {
                return true;
            }
            return false;
        }
               
        public static Task<bool> SaveNewClassAsync(ClassModel newClass)
        {
            return Task.Run<bool>(() =>
            {
                try
                {
                    string insertCurrClass =
                        "IF EXISTS (SELECT * FROM [School].[Class] WHERE UPPER(NameOfClass) = '" + newClass.NameOfClass + "') " +
                        "RAISERROR ('Class already exists. Consider using a new class Name.',17,0) " +
                        "ELSE BEGIN declare @id int; " +
                        "SET @id = [dbo].GetNewID('School.Class') " +
                        "INSERT INTO [School].[Class] (ClassID,NameOfClass) VALUES (@id,'" + newClass.NameOfClass + "') SELECT @id END";

                    return DataAccessHelper.ExecuteNonQuery(insertCurrClass);
                }
                catch
                {
                    return false;
                }
            });
        }
        
        public static bool RemoveSubject(string SubjectID)
        {
            bool resq = false;
            try
            {
                string insertCurrClass =
            "DELETE FROM [School].[Subject] WHERE SubjectID = '" + SubjectID +
            "' ";

                resq = DataAccessHelper.ExecuteNonQuery(insertCurrClass);
            }
            catch
            {
            }
            return resq;
        }

        public static bool RemoveStudent(string StudentID)
        {
            bool resq = false;
            try
            {
                string insertCurrClass =
            "DELETE FROM [School].[Student] WHERE StudentID = '" + StudentID +
            "' ";

                resq = DataAccessHelper.ExecuteNonQuery(insertCurrClass);
            }
            catch
            {
            }
            return resq;
        }
        
        internal static object GetAllRegisteredExams(string classID)
        {
            throw new NotImplementedException();
        }

        internal static string GetClassIDFromStudent(string studentID)
        {
            string SELECTSTR =
                       "SELECT ClassID FROM [School].[Student] WHERE StudentID='" + studentID + "'";
            return DataAccessHelper.ExecuteScalar(SELECTSTR);
        }

        internal static object GetAllRegisteredSubjects(string classID)
        {
            throw new NotImplementedException();
        }

        public static ObservableCollection<dynamic> FindStudent(string searchText)
        {
            ObservableCollection<dynamic> tempCls = new ObservableCollection<dynamic>();
            string selectStr = "";
            if (string.IsNullOrWhiteSpace(searchText))
            {
                selectStr =
                    "SELECT s.StudentID,s.NameOfStudent,c.NameOfClass FROM [School].[Student] s " +
                "LEFT OUTER JOIN [Users].Class c " +
                "ON s.ClassID = c.ClassID \r\n" +
                    "ORDER BY s.ModifiedDate DESC";
            }
            else
            {

                selectStr =
                   "SELECT s.StudentID,s.NameOfStudent,c.NameOfClass FROM [School].[Student] s " +
               "LEFT OUTER JOIN [Users].Class c " +
               "ON s.ClassID = c.ClassID WHERE ";
                string dtd =
    "SELECT column_name as ColumnName from INFORMATION_SCHEMA.columns WHERE table_schema='Users' and table_name='Student'";
                ObservableCollection<string> obsTemp = DataAccessHelper.CopyFromDBtoObservableCollection(dtd);
                if (obsTemp.Count > 0)
                {
                    for (int i = 0; i < obsTemp.Count; i++)
                    {
                        if (i != obsTemp.Count - 1)
                            selectStr += "s." + obsTemp[i] + " LIKE'%" + searchText + "%' OR ";
                        else
                            selectStr += "s." + obsTemp[i] + " LIKE'%" + searchText + "%' ";
                    }
                }
            }
            DataTable tempColl = DataAccessHelper.ExecuteNonQueryWithResultTable(selectStr);
            
            foreach (DataRow dtr in tempColl.Rows)
            {
                bool t = false;
                foreach (dynamic px in tempCls)
                {
                    if (px.StudentID == dtr[0].ToString())
                        t = true;
                }
                if (!t)
                    tempCls.Add(new { StudentID = dtr[0].ToString(), NameOfStudent = dtr[1].ToString(), NameOfClass = dtr[2].ToString() });
            }
            return tempCls;
        }

        public static Task<int> GetNewStudentIDAsync()
        {
            return DataAccessHelper.GetNewIDAsync("School.Student");
        }

        public static Task<int> GetNewStaffIDAsync()
        {
            return DataAccessHelper.GetNewIDAsync("School.Staff");
        }
                
        public static Task<ObservableCollection<ClassModel>> GetAllClassesAsync()
        {
            return Task.Run<ObservableCollection<ClassModel>>(() =>
            {
                string selectStr =
                   "SELECT ClassID,NameOfClass"+
                   " FROM [School].[Class]";
                ObservableCollection<ClassModel> allClasses = new ObservableCollection<ClassModel>();
                DataTable r = DataAccessHelper.ExecuteNonQueryWithResultTable(selectStr);
                if (r.Rows.Count != 0)
                {
                    ClassModel CurrentClass;
                    foreach (DataRow dtr in r.Rows)
                    {
                        CurrentClass = new ClassModel();
                        CurrentClass.ClassID = int.Parse(dtr[0].ToString());
                        CurrentClass.NameOfClass = dtr[1].ToString();
                        allClasses.Add(CurrentClass);
                    }
                }
                return allClasses;
            }
            );
        }

        public static Task<bool> SaveNewSubjectAsync(SubjectModel newSubject)
        {
            return Task.Run<bool>(() =>
            {
                try
                {
                    string insertCurrClass =
                        "INSERT INTO [School].[Subject] (SubjectID,NameOfSubject,MaximumScore)" +
                        " VALUES (" + newSubject.SubjectID + ",'" + newSubject.NameOfSubject + "','" +
                        newSubject.MaximumScore + "')";

                    return DataAccessHelper.ExecuteNonQuery(insertCurrClass);
                }
                catch
                {
                }
                return false;
            });
        }

        public static Task<bool> SaveNewStudentAsync(StudentModel student)
        {
            return Task.Run<bool>(() =>
            {
                string INSERTSTR = "USE Starehe" + Environment.NewLine +
                    "INSERT INTO [School].[Student] (StudentID,FirstName,LastName,MiddleName,DateOfBirth,DateOfAdmission," +
                    "NameOfGuardian,GuardianPhoneNo,Email,Address,City,PostalCode,ClassID," +
                    "DormitoryID ,BedNo,PreviousSchool,SPhoto) " +
                    "VALUES("+student.StudentID+",'"+
                    student.FirstName+"','"+
                    student.LastName+"','"+
                    student.MiddleName+"','"+
                    student.DateOfBirth+"','"+
                    student.DateOfAdmission+"','"+
                    student.NameOfGuardian+"','"+
                    student.GuardianPhoneNo+"','"+
                    student.Email+"','"+
                    student.Address+"','"+
                    student.City+"','"+
                    student.PostalCode+"','"+
                    student.ClassID+"','"+
                    student.DormitoryID+"','"+
                    student.BedNo+"','"+
                    student.PrevSchool+"',?)";

                ObservableCollection<OleDbParameter> paramColl = new ObservableCollection<OleDbParameter>();
                
                paramColl.Add(new OleDbParameter("", student.SPhoto));

                return DataAccessHelper.ExecuteNonQueryWithParameters(INSERTSTR, paramColl);
            });
        }
        public static Task<ObservableCollection<StaffModel>> GetAllStaffAsync()
        {
            return Task.Run<ObservableCollection<StaffModel>>(() =>
            {
                string selectStr =
                   "SELECT TOP 1000000 StaffID,Name,NationalID,DateOfAdmission,PhoneNo,Email," +
                   "Address,City,PostalCode,SPhoto" +
                   " FROM [School].[Staff]";
                ObservableCollection<StaffModel> allStaff = new ObservableCollection<StaffModel>();
                DataTable r = DataAccessHelper.ExecuteNonQueryWithResultTable(selectStr);
                if (r.Rows.Count != 0)
                {
                    StaffModel CurrentStaff;
                    foreach (DataRow dtr in r.Rows)
                    {
                        CurrentStaff = new StaffModel();
                        CurrentStaff.StaffID = (int)dtr[0];
                        CurrentStaff.Name = dtr[1].ToString();
                        CurrentStaff.NationalID = dtr[2].ToString();
                        CurrentStaff.DateOfAdmission = DateTime.Parse(dtr[3].ToString());
                        CurrentStaff.PhoneNo = dtr[4].ToString();
                        CurrentStaff.Email = dtr[5].ToString();
                        CurrentStaff.Address = dtr[6].ToString();
                        CurrentStaff.City = dtr[7].ToString();
                        CurrentStaff.PostalCode = dtr[8].ToString();
                        CurrentStaff.SPhoto = dtr[9] as byte[];
                        allStaff.Add(CurrentStaff);
                    }
                }
                return allStaff;
            }
            );
        }
        public static Task<ObservableCollection<StudentModel>> GetAllStudentsAsync()
        {

            return Task.Run<ObservableCollection<StudentModel>>(() =>
            {
                string selectStr =
                   "SELECT TOP 1000000 StudentID,FirstName,LastName,MiddleName,ClassID,DateOfBirth," +
                   "DateOfAdmission,NameOfGuardian,GuardianPhoneNo,Email," +
                   "Address,City,PostalCode,DormitoryID,BedNo,PreviousSchool,SPhoto" +
                   " FROM [School].[Student]";
                ObservableCollection<StudentModel> allStudents = new ObservableCollection<StudentModel>();
                DataTable r = DataAccessHelper.ExecuteNonQueryWithResultTable(selectStr);
                if (r.Rows.Count != 0)
                {
                    StudentModel CurrentStudent;
                    foreach (DataRow dtr in r.Rows)
                    {
                        CurrentStudent = new StudentModel();
                        CurrentStudent.StudentID = (int)dtr[0];
                        CurrentStudent.FirstName = dtr[1].ToString();
                        CurrentStudent.MiddleName = dtr[3].ToString();
                        CurrentStudent.LastName = dtr[2].ToString();
                        CurrentStudent.ClassID = (int)dtr[4];
                        CurrentStudent.DateOfBirth = DateTime.Parse(dtr[5].ToString());
                        CurrentStudent.DateOfAdmission = DateTime.Parse(dtr[6].ToString());
                        CurrentStudent.NameOfGuardian = dtr[7].ToString();
                        CurrentStudent.GuardianPhoneNo = dtr[8].ToString();
                        CurrentStudent.Email = dtr[9].ToString();
                        CurrentStudent.Address = dtr[10].ToString();
                        CurrentStudent.City = dtr[11].ToString();
                        CurrentStudent.PostalCode = dtr[12].ToString();
                        CurrentStudent.DormitoryID = dtr[13].ToString();
                        CurrentStudent.BedNo = dtr[14].ToString();
                        CurrentStudent.PrevSchool = dtr[15].ToString();
                        CurrentStudent.SPhoto = dtr[16] as byte[];
                        allStudents.Add(CurrentStudent);
                    }
                }
                return allStudents;
            }
            );
        }


        public static Task<bool> SaveNewStaffAsync(StaffModel newStaff)
        {
            return Task.Run<bool>(() =>
            {
                string INSERTSTR = "USE Starehe" + Environment.NewLine +
                    "INSERT INTO [School].[Staff] (StaffID,Name,NationalID,DateOfAdmission,PhoneNo," +
                    "Email,Address,City,PostalCode,SPhoto) " +
                    "VALUES(" + newStaff.StaffID + ",'" +
                    newStaff.Name + "','" +
                    newStaff.NationalID + "','" +
                    newStaff.DateOfAdmission + "','" +
                    newStaff.PhoneNo + "','" +
                    newStaff.Email + "','" +
                    newStaff.Address + "','" +
                    newStaff.City + "','" +
                    newStaff.PostalCode + "',?)";

                ObservableCollection<OleDbParameter> paramColl = new ObservableCollection<OleDbParameter>();

                paramColl.Add(new OleDbParameter("", newStaff.SPhoto));

                return DataAccessHelper.ExecuteNonQueryWithParameters(INSERTSTR, paramColl);
            });
        }

        public static Task<bool> SaveNewFeesPaymentAsync(FeePaymentModel newPayment)
        {
            return Task.Run<bool>(() =>
            {
                string INSERTSTR = "USE Starehe" + Environment.NewLine +
                    "INSERT INTO [School].[FeesPayment] (FeesPaymentID,StudentID,AmountPaid,DatePaid) " +
                    "VALUES(" + newPayment.FeePaymentID + "," +
                    newPayment.StudentID + ",'" +
                    newPayment.AmountPaid + "','" +
                    newPayment.DatePaid.ToString(new CultureInfo("en-GB")) + "')";
                return DataAccessHelper.ExecuteNonQuery(INSERTSTR);
            });
        }

        internal static Task<string> GetNameOfStudent(int studentID)
        {
            return Task.Run<string>(() => 
            {
                string INSERTSTR = "USE Starehe" + Environment.NewLine +
                                   "SELECT [FirstName]+' '+[MiddleName]+' '+[LastName] FROM [School].[Student] WHERE StudentID='" + studentID + "'";
                return DataAccessHelper.ExecuteScalar(INSERTSTR);
            });
        }
                
        public static bool SearchAllStudentProperties(StudentModel student, string searchText)
        {
                Regex.CacheSize = 14;
                return
                    Regex.Match(student.StudentID.ToString(), searchText, RegexOptions.IgnoreCase).Success ||
                    Regex.Match(student.FirstName, searchText, RegexOptions.IgnoreCase).Success ||
                    Regex.Match(student.LastName, searchText, RegexOptions.IgnoreCase).Success ||
                    Regex.Match(student.MiddleName, searchText, RegexOptions.IgnoreCase).Success ||
                    Regex.Match(student.NameOfGuardian, searchText, RegexOptions.IgnoreCase).Success ||
                    Regex.Match(student.Address, searchText, RegexOptions.IgnoreCase).Success ||
                    Regex.Match(student.BedNo, searchText, RegexOptions.IgnoreCase).Success ||
                    Regex.Match(student.City, searchText, RegexOptions.IgnoreCase).Success ||
                    Regex.Match(student.ClassID.ToString(), searchText, RegexOptions.IgnoreCase).Success ||
                    Regex.Match(student.DormitoryID, searchText, RegexOptions.IgnoreCase).Success ||
                    Regex.Match(student.Email, searchText, RegexOptions.IgnoreCase).Success ||
                    Regex.Match(student.NameOfStudent, searchText, RegexOptions.IgnoreCase).Success ||
                    Regex.Match(student.PostalCode, searchText, RegexOptions.IgnoreCase).Success ||
                    Regex.Match(student.PrevSchool, searchText, RegexOptions.IgnoreCase).Success;          
        }

        public static bool SearchAllStaffProperties(StaffModel staff, string searchText)
        {
            Regex.CacheSize = 14;
            return
                Regex.Match(staff.StaffID.ToString(), searchText, RegexOptions.IgnoreCase).Success ||
                Regex.Match(staff.Name, searchText, RegexOptions.IgnoreCase).Success ||
                Regex.Match(staff.NationalID, searchText, RegexOptions.IgnoreCase).Success ||
                Regex.Match(staff.Address, searchText, RegexOptions.IgnoreCase).Success ||
                Regex.Match(staff.City, searchText, RegexOptions.IgnoreCase).Success ||
                Regex.Match(staff.Email, searchText, RegexOptions.IgnoreCase).Success ||                
                Regex.Match(staff.PhoneNo, searchText, RegexOptions.IgnoreCase).Success ||
                Regex.Match(staff.PostalCode, searchText, RegexOptions.IgnoreCase).Success;
        }

        public static Task<bool> SaveNewFeesStructureAsync(FeesStructureModel currrentStruct)
        {
            return Task.Run<bool>(() =>
            {
                string insertStr = "BEGIN TRANSACTION\r\n" +
                    "INSERT INTO [School].[FeesStructureHeader] (FeesStructureID,ClassID, StartDate)" +
                                " VALUES (" + currrentStruct.FeesStructureID +
                                "," + currrentStruct.ClassID +
                                ",'" + currrentStruct.StartDate.ToString(new CultureInfo("en-GB")) + "')\r\n";

                foreach (FeesStructureEntryModel entry in currrentStruct.Entries)
                    insertStr += "INSERT INTO [School].[FeesStructureDetail] (FeesStructureID,Name,Amount)" +
                        " VALUES (" + currrentStruct.FeesStructureID +
                        ",'" + entry.Name +
                        "','" + entry.Amount +
                        "')\r\n";
                insertStr += " COMMIT";

                return DataAccessHelper.ExecuteNonQuery(insertStr);
            });
        }

        public static Task<FeesStructureModel> GetFeesStructureAsync(int currentClassID, DateTime currentDate)
        {
            return Task.Run<FeesStructureModel>(() =>
            {
                FeesStructureModel tempModel = new FeesStructureModel();
                string fdate = currentDate.Date.ToString(new CultureInfo("en-GB"));
                string selectStr = "DECLARE @id int=([dbo].GetFeesStructureID(" + currentClassID + ",'" + fdate + "'))\r\n"+
                    "SELECT @id";

                int feesStructureID = (int)DataAccessHelper.ExecuteObjectScalar(selectStr);
                if (feesStructureID <= 0)
                    return tempModel;

                selectStr = "SELECT Name, Amount FROM [School].[FeesStructureDetail] WHERE FeesStructureID =" + feesStructureID;

                DataTable dt = DataAccessHelper.ExecuteNonQueryWithResultTable(selectStr);
                FeesStructureEntryModel fssem;
                foreach (DataRow dtr in dt.Rows)
                {
                    fssem = new FeesStructureEntryModel();
                    fssem.Amount = decimal.Parse(dtr[1].ToString());
                    fssem.Name = dtr[0].ToString();
                    tempModel.Entries.Add(fssem);
                }
                return tempModel;
            });
        }

        public static Task<ObservableCollection<SubjectModel>> GetSubjectsRegistredToClassAsync(int selectedClassID, DateTime currentDate)
        {
            return Task.Run<ObservableCollection<SubjectModel>>(() =>
                {
                    ObservableCollection<SubjectModel> allSubjects = new ObservableCollection<SubjectModel>();
                    string fdate = currentDate.ToString(new CultureInfo("en-GB"));                    
                    string selectStr = "DECLARE @id int\r\n SET @id=("+
                        "SELECT SubjectSetupID FROM " +
                    "[School].[SubjectSetupHeader] WHERE ClassID=" + selectedClassID + " AND '"
                    + fdate + "'>= StartDate AND '"+fdate
                    + "'< ISNULL(EndDate,DATEADD(dd,1,'" + fdate+"')))\r\n"+
                    "SELECT ISNULL(@id,0)";

                    int subjectSetupID = (int)DataAccessHelper.ExecuteObjectScalar(selectStr);                    

                    if (subjectSetupID <= 0)
                        return allSubjects;

                    selectStr = "SELECT ssd.SubjectID,s.NameOfSubject,s.MaximumScore FROM [School].[SubjectSetupDetail] ssd "+
                    "LEFT OUTER JOIN [School].[Subject] s on ssd.SubjectID = s.SubjectID WHERE ssd.SubjectSetupID ="+subjectSetupID;
                    
                    DataTable dt = DataAccessHelper.ExecuteNonQueryWithResultTable(selectStr);
                    SubjectModel sub;
                    foreach (DataRow dtr in dt.Rows)
                    {
                        sub = new SubjectsSetupEntryModel();
                        sub.SubjectID = (int)dtr[0];
                        sub.NameOfSubject = dtr[1].ToString();
                        sub.MaximumScore = decimal.Parse(dtr[2].ToString());
                        allSubjects.Add(sub);
                    }

                    return allSubjects;
                });
        }
        
        public static Task<ObservableCollection<FeePaymentModel>> GetRecentPaymentsAsync(int studentID)
        {
            return Task.Run<ObservableCollection<FeePaymentModel>>(() => 
            {
                ObservableCollection<FeePaymentModel> temp = new ObservableCollection<FeePaymentModel>();
                string selectStr = "SELECT TOP 20 AmountPaid, DatePaid FROM [School].[FeesPayment] WHERE StudentID =" +
                    studentID + " ORDER BY [DatePaid] desc";

                DataTable dt = DataAccessHelper.ExecuteNonQueryWithResultTable(selectStr);
                FeePaymentModel fpm;
                foreach (DataRow dtr in dt.Rows)
                {
                    fpm = new FeePaymentModel();
                    fpm.AmountPaid = decimal.Parse(dtr[0].ToString());
                    fpm.StudentID = studentID;
                    fpm.DatePaid = DateTime.Parse(dtr[1].ToString());
                    temp.Add(fpm);
                }

                return temp;
            });
        }

        public static Task<bool> SaveNewExamAsync(ExamModel newExam)
        {
            return Task.Run<bool>(() =>
            {
                string insertStr = "BEGIN TRANSACTION\r\n" +
                    "INSERT INTO [School].[ExamHeader] (ExamID,ClassID,NameOfExam)" +
                                " VALUES (" + newExam.ExamID +
                                "," + newExam.ClassID +",'" + newExam.NameOfExam + "')\r\n";

                foreach (ExamSubjectEntryModel entry in newExam.Entries)
                    insertStr += "INSERT INTO [School].[ExamDetail] (ExamID,SubjectID,ExamDateTime)" +
                        " VALUES (" + newExam.ExamID +
                        "," + entry.SubjectID +
                        ",'" + entry.ExamDateTime.ToString("d", new CultureInfo("en-GB")) +
                        "')\r\n";
                insertStr += " COMMIT";

                return DataAccessHelper.ExecuteNonQuery(insertStr);
            });
        }

        public static Task<bool> SaveNewSubjectSetupAsync(SubjectsSetupModel subjectsSetup)
        {
            return Task.Run<bool>(() =>
            {
                string insertStr = "BEGIN TRANSACTION\r\n" +
                    "INSERT INTO [School].[SubjectSetupHeader] (SubjectSetupID,ClassID,StartDate)" +
                                " VALUES (" + subjectsSetup.SubjectSetupID +
                                "," + subjectsSetup.ClassID +
                                ",'" + subjectsSetup.StartDate.ToString("d",new CultureInfo("en-GB")) + "')\r\n";

                foreach (SubjectsSetupEntryModel entry in subjectsSetup.Entries)
                    insertStr += "INSERT INTO [School].[SubjectSetupDetail] (SubjectSetupID,SubjectID)" +
                        " VALUES (" + subjectsSetup.SubjectSetupID +
                        "," + entry.SubjectID + ")\r\n";
                insertStr += " COMMIT";

                return DataAccessHelper.ExecuteNonQuery(insertStr);
            });
        }

        public static Task<bool> SaveNewClassSetupAsync(ClassesSetupModel classesSetup)
        {
            return Task.Run<bool>(() =>
            {
                string insertStr = "BEGIN TRANSACTION\r\n" +
                    "INSERT INTO [School].[ClassSetupHeader] (ClassSetupID,StartDate)" +
                                " VALUES (" + classesSetup.ClassSetupID +
                                ",'" + classesSetup.StartDate.ToString("d", new CultureInfo("en-GB")) + "')\r\n";

                foreach (ClassesSetupEntryModel entry in classesSetup.Entries)
                    insertStr += "INSERT INTO [School].[ClassSetupDetail] (ClassSetupID,ClassID)" +
                        " VALUES (" + classesSetup.ClassSetupID +
                        "," + entry.ClassID + ")\r\n";
                insertStr += " COMMIT";

                return DataAccessHelper.ExecuteNonQuery(insertStr);
            });
        }

        public static Task<int> GetClassIDFromStudentID(int selectedStudentID)
        {
            return Task.Run<int>(() =>
                {
                    string selectStr = "IF EXISTS(SELECT ClassID FROM [School].[Student] WHERE StudentID = " + selectedStudentID + ")\r\n" +
                        "SELECT ClassID FROM [School].[Student] WHERE StudentID = " + selectedStudentID + "\r\nELSE SELECT 0";
                    string res = DataAccessHelper.ExecuteScalar(selectStr);
                    int temp = int.Parse(res);
                    return temp;
                });
        }

        public static Task<ObservableCollection<ExamModel>> GetExamsByClass(int classID)
        {
            return Task.Run<ObservableCollection<ExamModel>>(async () =>
            {
                ObservableCollection<ExamModel> temp = new ObservableCollection<ExamModel>();
                string selecteStr = "SELECT ExamID,NameOfExam FROM [School].[ExamHeader] WHERE ClassID=" + classID;
                DataTable dt = DataAccessHelper.ExecuteNonQueryWithResultTable(selecteStr);
                ExamModel em;
                List<Task<KeyValuePair<int, ObservableCollection<ExamSubjectEntryModel>>>> tems = 
                    new List<Task<KeyValuePair<int, ObservableCollection<ExamSubjectEntryModel>>>>(dt.Rows.Count);
               
                foreach (DataRow dtr in dt.Rows)
                {
                    em = new ExamModel();
                    em.ExamID = int.Parse(dtr[0].ToString());
                    em.NameOfExam = dtr[1].ToString();
                    tems.Add(GetExamEntries(em.ExamID));
                    temp.Add(em);
                }
                KeyValuePair<int, ObservableCollection<ExamSubjectEntryModel>>[] entriesCollection = 
                    await Task.WhenAll(tems);
                foreach (ExamModel ems in temp)
                {
                    var entries = from entry in entriesCollection.AsParallel()
                                  where entry.Key == ems.ExamID
                                  select entry;
                    ems.Entries = entries.First().Value;
                }
                return temp;
            });
        }

        public static Task<ExamModel> GetExamAsync(int examID)
        {
            return Task.Run<ExamModel>(async() => 
            {
                ExamModel temp = new ExamModel();
                string selecteStr = "SELECT ClassID,NameOfExam FROM [School].[ExamHeader] WHERE ExamID=" + examID;

                DataTable dt = DataAccessHelper.ExecuteNonQueryWithResultTable(selecteStr);
                if (dt.Rows.Count <= 0)
                    return temp;
                temp.ExamID = examID;
                temp.ClassID = int.Parse(dt.Rows[0][0].ToString());
                temp.NameOfExam = dt.Rows[0][1].ToString();
                temp.Entries = (await GetExamEntries(examID)).Value;
                return temp;
            }); 
        }

        private static Task<KeyValuePair<int,ObservableCollection<ExamSubjectEntryModel>>> GetExamEntries(int examID)
        {
            return Task.Run<KeyValuePair<int, ObservableCollection<ExamSubjectEntryModel>>>(() =>
                {
                    string selectStr = "SELECT ed.SubjectID, s.NameOfSubject, ed.ExamDateTime FROM [School].[ExamDetail] ed LEFT OUTER JOIN " +
                        "[School].[Subject] s ON (ed.SubjectID = s.SubjectID) WHERE ed.ExamID =" + examID;
                    KeyValuePair<int, ObservableCollection<ExamSubjectEntryModel>> temp;
                    
                    DataTable dt = DataAccessHelper.ExecuteNonQueryWithResultTable(selectStr);
                    ObservableCollection<ExamSubjectEntryModel> entries = new ObservableCollection<ExamSubjectEntryModel>();
                    ExamSubjectEntryModel esem;
                    foreach (DataRow dtr in dt.Rows)
                    {
                        esem = new ExamSubjectEntryModel();
                        esem.SubjectID = int.Parse(dtr[0].ToString());
                        esem.NameOfSubject = dtr[1].ToString();
                        esem.ExamDateTime = DateTime.Parse(dtr[2].ToString(), new CultureInfo("en-GB"));
                        entries.Add(esem);
                    }
                    temp = new KeyValuePair<int, ObservableCollection<ExamSubjectEntryModel>>(examID, entries);
                    return temp;
                });
        }

        public static Task<bool> SaveNewExamResultAsync(ExamResultModel newResult)
        {
            return Task.Run<bool>(() =>
            {
                string insertStr = "BEGIN TRANSACTION\r\n" +
                    "INSERT INTO [School].[ExamResultHeader] (ExamResultID,ExamID,StudentID)" +
                                " VALUES (" + newResult.ExamResultID +
                                "," + newResult.ExamID + "," + newResult.StudentID + ")\r\n";

                foreach (ExamResultSubjectModel entry in newResult.Entries)
                    insertStr += "INSERT INTO [School].[ExamResultDetail] (ExamResultID,SubjectID,Score,Remarks)" +
                        " VALUES (" + newResult.ExamResultID +
                        "," + entry.SubjectID + ",'"+entry.Score+"','"+entry.Remarks+"')\r\n";
                insertStr += " COMMIT";

                return DataAccessHelper.ExecuteNonQuery(insertStr);
            });
        }

        public static Task<ExamResultModel> GetExamResultAync(int studentID, int examID)
        {
            return Task.Run<ExamResultModel>(() =>
                {
                    ExamResultModel temp = new ExamResultModel();
                    temp.StudentID = studentID;
                    temp.ExamID = examID;
                    string selectStr =
                        "IF EXISTS(SELECT ExamResultID FROM [School].[ExamResultHeader] WHERE ExamID=" + examID +
                            " AND StudentID=" + studentID + " AND IsActive=1)\r\n SELECT ExamResultID FROM [School].[ExamResultHeader] WHERE ExamID=" + examID +
                            " AND StudentID=" + studentID+" AND IsActive=1\r\nELSE SELECT 0";
                    string res = DataAccessHelper.ExecuteScalar(selectStr);
                    int id = int.Parse(res);
                    if (id == 0)
                        return temp;
                    temp.ExamResultID = id;
                    selectStr = "SELECT erd.SubjectID, s.NameOfSubject, erd.Score, erd.Remarks FROM [School].[ExamResultDetail] erd " +
                        "LEFT OUTER JOIN [School].[Subject] s ON(erd.SubjectID=s.SubjectID) WHERE erd.ExamResultID=" + id;
                    ExamResultSubjectModel ersm;
                    DataTable dt = DataAccessHelper.ExecuteNonQueryWithResultTable(selectStr);
                    foreach(DataRow dtr in dt.Rows)
                    {
                        ersm = new ExamResultSubjectModel();
                        ersm.ExamResultID = id;
                        ersm.SubjectID = int.Parse(dtr[0].ToString());
                        ersm.NameOfSubject = dtr[1].ToString();
                        ersm.Score = decimal.Parse(dtr[2].ToString());
                        ersm.Remarks = dtr[3].ToString();
                        temp.Entries.Add(ersm);
                    }
                    return temp;
                }
                );
        }

        public static Task<ClassModel> GetClassAsync(int classID)
        {
            return Task.Run<ClassModel>(() =>
            {
                ClassModel temp = new ClassModel();
                string selecteStr = "SELECT ClassID,NameOfClass FROM [School].[Class] WHERE ClassID=" + classID;

                DataTable dt = DataAccessHelper.ExecuteNonQueryWithResultTable(selecteStr);
                if (dt.Rows.Count <= 0)
                    return temp;
                temp.ClassID = int.Parse(dt.Rows[0][0].ToString());
                temp.NameOfClass = dt.Rows[0][1].ToString();
                return temp;
            });
        }

        public static Task<bool> SaveNewEventAsync(EventModel em)
        {
            return Task.Run<bool>(() =>
            {
                string insertStr = "BEGIN TRANSACTION\r\n" +
                    "INSERT INTO [School].[Event] (Name,StartDateTime,EndDateTime,Location,Subject,Message)" +
                    " VALUES ('" + em.Name +
                    "','" + em.StartDateTime.ToString(new CultureInfo("en-GB")) + "','" +
                    em.EndDateTime.ToString(new CultureInfo("en-GB")) + "','" +
                    em.Location + "','" + em.Subject + "','" + em.Message + "')\r\n COMMIT";
                return DataAccessHelper.ExecuteNonQuery(insertStr);
            });
        }

        public static Task<ObservableCollection<EventModel>> GetUpcomingEvents()
        {
            return Task.Run<ObservableCollection<EventModel>>(() => 
            {
                string selectStr = "SELECT Name, StartDateTime, EndDateTime, Location, Subject, Message " +
                    "FROM [School].[Event] WHERE StartDateTime >= '" + DateTime.Now.ToString(new CultureInfo("en-GB")) + "'";
                DataTable dt = DataAccessHelper.ExecuteNonQueryWithResultTable(selectStr);
                ObservableCollection<EventModel> temp = new ObservableCollection<EventModel>();
                EventModel em;
                foreach (DataRow dtr in dt.Rows)
                {
                    em = new EventModel();
                    em.Name = dtr[0].ToString();
                    em.StartDateTime = DateTime.Parse(dtr[1].ToString());
                    em.EndDateTime = DateTime.Parse(dtr[2].ToString());
                    em.Location = dtr[3].ToString();
                    em.Subject = dtr[4].ToString();
                    em.Message = dtr[5].ToString();
                    temp.Add(em);
                }
                return temp;
            });
        }

        public static Task<ObservableCollection<EventModel>> GetAllEvents()
        {
            return Task.Run<ObservableCollection<EventModel>>(() =>
            {
                string selectStr = "SELECT Name, StartDateTime, EndDateTime, Location, Subject, Message " +
                    "FROM [School].[Event] ORDER BY StartDateTime desc";
                DataTable dt = DataAccessHelper.ExecuteNonQueryWithResultTable(selectStr);
                ObservableCollection<EventModel> temp = new ObservableCollection<EventModel>();
                EventModel em;
                foreach (DataRow dtr in dt.Rows)
                {
                    em = new EventModel();
                    em.Name = dtr[0].ToString();
                    em.StartDateTime = DateTime.Parse(dtr[1].ToString());
                    em.EndDateTime = DateTime.Parse(dtr[2].ToString());
                    em.Location = dtr[3].ToString();
                    em.Subject = dtr[4].ToString();
                    em.Message = dtr[5].ToString();
                    temp.Add(em);
                }
                return temp;
            });
        }

        public static Task<bool> SaveNewGalleryItemAsync(Uri mediaSource)
        {
            return Task.Run<bool>(() =>
                {
                    //throw new NotImplementedException();
                    return true;
                });
        }
    }

}
